user

Table Name: user

The user table stores information about users within the system, including their personal details, account status, role, and relationships to other users.

---

Columns

Column Name	Data Type	Constraints	Description
mappedId	int(11)	Primary Key, Not Null, Foreign Key	The unique identifier for the user in the system.
createdDate	datetime	Nullable	The date and time when the user account was created.
description	varchar(255)	Nullable	A brief description of the user.
email	varchar(50)	Nullable	The email address of the user.
firstName	varchar(30)	Not Null	The first name of the user.
lastEditedDate	datetime	Nullable	The date and time when the user details were last edited.
lastName	varchar(30)	Nullable	The last name of the user.
middleName	varchar(30)	Nullable	The middle name of the user.
password	varchar(255)	Nullable	The password for the user account (usually encrypted).
status	varchar(20)	Nullable	The current status of the user (e.g., active, inactive).
username	varchar(50)	Unique, Not Null	The username chosen by the user.
createdByUserId	int(11)	Nullable, Foreign Key	The user ID of the person who created this user account.
lastEditedByUserId	int(11)	Nullable, Foreign Key	The user ID of the person who last edited this user account.
passEditedDate	datetime	Nullable	The date and time when the user password was last edited.
deviceType	varchar(45)	Nullable	The type of device used by the user (e.g., mobile, desktop).
dateRegistered	datetime	Nullable	The date and time when the user registered for the system.
gender	varchar(15)	Nullable	The gender of the user.
nic	varchar(20)	Nullable	The National Identification Card (NIC) number for the user.
organization	varchar(45)	Nullable	The organization with which the user is associated (if any).
isFederalUser	bit(1)	Nullable	Indicates if the user is a federal user (1 for yes, 0 for no).
isVaccinator	bit(1)	Nullable	Indicates if the user is a vaccinator (1 for yes, 0 for no).
token	varchar(255)	Nullable	A token used for authentication or session management.

---

14.2 Indexes

1. Primary Key:
   • mappedId: Uniquely identifies each user in the system.
2. Unique Key:
   • username: Ensures that each username is unique across users.
3. Foreign Keys:
   • FK36EBCBC79271AF:
     • References idmapper(mappedId) for the mappedId column.
     • Ensures the user's ID exists in the idmapper table.
   • user_createdByUserId_user_mappedId_FK:
     • References user(mappedId) for the createdByUserId column.
     • Links the user to the user who created their account.
   • user_lastEditedByUserId_user_mappedId_FK:
     • References user(mappedId) for the lastEditedByUserId column.
     • Links the user to the user who last edited their account details.

---

Foreign Key Relations

1. FK36EBCBC79271AF:
   • Referenced Table: idmapper
   • Referenced Column: mappedId
   • Description: Ensures that each user has a corresponding mappedId in the idmapper table, enforcing data consistency across related tables.
2. user_createdByUserId_user_mappedId_FK:
   • Referenced Table: user
   • Referenced Column: mappedId
   • Description: Links the user to the individual who created their account. This establishes a parent-child relationship between users.
3. user_lastEditedByUserId_user_mappedId_FK:
   • Referenced Table: user
   • Referenced Column: mappedId
   • Description: Links the user to the individual who last edited their account details, tracking changes made by specific users.

---

Usage Notes

• Purpose:
  • The user table maintains basic user information for individuals interacting with the system, including roles (e.g., vaccinator) and associated metadata like gender and status.
• Key Attributes:
  • mappedId: A unique identifier for each user.
  • username: A unique login identifier.
  • createdByUserId: The user who created this account.
  • lastEditedByUserId: The user who last edited this account.